import pymysql

class SqlUtil:

    #创建数据库连接
    def connect(self):
        db = pymysql.connect(host='localhost',user='root',password='123456',db='studb',charset='utf8')
        return db

    #查询
    def select(self):
        db = self.connect()
        cursor = db.cursor()
        cursor.execute("SELECT s.id,s.`stuName`,s.`age`,s.`sex` ,c.className FROM stu s JOIN class c ON s.`classId` = c.`id` ORDER BY s.id")
        datas = cursor.fetchall()
        db.close()
        return datas

    # 添加
    def add(self,para):
        db = self.connect()
        cursor = db.cursor()
        sql = "INSERT INTO stu(stuName,age,sex,classId) VALUES('%s','%d','%s','%d')"%(para)
        try:
            cursor.execute(sql)
            db.commit()
            print("学生记录添加成功！")
        except Exception as err:
            db.rollback()
            print("SQL执行错误！", err)
        db.close()

    # 修改
    def update(self,para):
        db = self.connect()
        cursor = db.cursor()
        sql = "UPDATE stu SET stuName='%s',age='%d',sex='%s',classId='%d' WHERE id='%d'"%(para)
        try:
            cursor.execute(sql)
            db.commit()
            print("学员信息修改成功！")
        except Exception as err:
            db.rollback()
            print("SQL执行错误！", err)
        db.close()

    # 删除
    def delete(self,id):
        db = self.connect()
        cursor = db.cursor()
        sql = "DELETE FROM stu WHERE id = '%d'"%(id)
        try:
            cursor.execute(sql)
            db.commit()
            print("该学生记录删除成功！")
        except Exception as err:
            db.rollback()
            print("SQL执行错误！", err)


sql = sqlUtil()
def showInfos():
    stuList = sql.select()
    if len(stuList)==0:
        print("========== 没有学员信息可以输出！=============")
        return
    print("|{0:<2}| {1:10}| {2:<5}| {3:<10}| {4:<10}|".format("学号", "姓名", "年龄", "性别","班级"))
    print("-" * 40)
    for stu in stuList:
        print("|{0:<2}| {1:10}| {2:<5}| {3:<10}| {4:<10}|".format(stu[0],stu[1],stu[2],stu[3],stu[4]))

while True:
    # 输出初始界面
    print("=" * 12, "学员管理系统", "=" * 14)
    print("1、查询学生信息")
    print("2、学员信息添加")
    print("3、学员信息修改")
    print("4、学员信息删除")
    print("5、退出")

    print("=" * 40)
    key = input("请输入对应的选择：")
    #根据键盘值，判断并执行对应的操作
    if key == '1':
        print("=" * 12, "学员信息浏览", "=" * 14)
        showInfos()
        input("按回车键继续：")
    elif key == '2':
        print("=" * 12, "学员信息添加", "=" * 14)
        para = ()
        stuName =input( '请输入学员的姓名：')
        age = input('请输入学员的年龄：')
        sex = input('请输入学员的性别：')
        classId = input('请输入学员的所属班级：')
        para = (stuName,int(age),sex,int(classId))
        sql.add(para)
        input("按回车键继续：")
    elif key == '3':
        print("=" * 12, "学员信息修改", "=" * 14)
        id = input("请输入需要修改信息的学号：")
        stuName = input('请输入学员的姓名：')
        age = input('请输入学员的年龄：')
        sex = input('请输入学员的性别：')
        classId = input('请输入学员的所属班级：')
        para = (stuName,int(age),sex,int(classId),int(id))
        sql.update(para)
        input("按回车键继续：")
    elif key == '4':
        print("=" * 12, "学员信息删除", "=" * 14)
        showInfos()
        sid = input("请输入你要删除的信息id号：")
        sql.delete(int(sid))
        input("按回车键继续：")
    elif key == '5':
        print("=" * 12, "再见", "=" * 14)
        break
    else:
        print("======== 无效的键盘输入！ ==========")